/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.enrstu.repository;

import me.zhengjie.modules.enrstu.domain.ReviewInformation;
import me.zhengjie.modules.enrstu.service.dto.ReviewAlreadyOrAcceptedInfoListDto;
import me.zhengjie.modules.enrstu.service.dto.ReviewWaitInfoListDto;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://eladmin.vip
* @author yyx
* @date 2024-11-01
**/
public interface ReviewInformationRepository extends JpaRepository<ReviewInformation, String>, JpaSpecificationExecutor<ReviewInformation> {
    /**
     * 获取待审核列表
     * @return /
     */
    @Query(name = "ReviewInformation.findReviewWaitInfoList", nativeQuery = true)
    List<ReviewWaitInfoListDto> findReviewWaitInfoList(
            String childrenName,
            String childrenIdentityCard,
            String schoolDistrictName,
            String schoolName,
            Boolean isRepeatedReview
            );


    /**
     * 获取已审核列表
     * @return /
     */
    @Query(name = "ReviewInformation.findReviewAlreadyInfoList", nativeQuery = true)
    List<ReviewAlreadyOrAcceptedInfoListDto> findReviewAlreadyInfoList(
            String childrenName,
            String childrenIdentityCard,
            String schoolDistrictName,
            String schoolName,
            String reviewStatus,
            Boolean isRepeatedReview
            );

    /**
     *获取录取列表
     * @return  /
     */
    @Query(name = "ReviewInformation.findReviewAcceptedInfoList", nativeQuery = true)
    List<ReviewAlreadyOrAcceptedInfoListDto> findReviewAcceptedInfoList(
            String childrenName,
            String childrenIdentityCard,
            String schoolDistrictName,
            String schoolName,
            String reviewStatus);

    /**
     * 获取列表总数量
     * @param isRepeatedReview /
     * @return /
     */
    @Query(value = "SELECT COUNT(*) FROM " +
            "info_review_information ri " +
            "LEFT JOIN " +
            "info_review_information_log lg ON lg.id = ri.admission_fresh_id " +
            "WHERE " +
            " ri.review_stage = '录取'",nativeQuery = true)
    long countAccepted(Boolean isRepeatedReview);
    /**
     * 判断儿童id是否存在
     * @param childId /
     * @return /
     */
    boolean existsByChildId(String childId);
    /**
     * 根据儿童id查询
     * @param childId /
     * @return /
     */
    ReviewInformation findByChildId(String childId);
    /**
     * 动态获取待初审核列表
     * @param deptId /
     * @return /
     */
    @Query("FROM ReviewInformation re LEFT JOIN ChildrenInformation chi ON re.childId = chi.id " +
                    "LEFT JOIN School sch ON sch.schoolId = chi.schoolId WHERE sch.deptId =?1 AND re.reviewStatus = '待初审' AND re.reviewStage = '学校初审' ORDER BY re.gmtCreate")
    List<ReviewInformation>  findReWaitDynamicsList(Long deptId);



    /**
     * 动态获取待复审核列表
     * @param deptId /
     * @return /
     */
    @Query("FROM ReviewInformation re LEFT JOIN ChildrenInformation chi ON re.childId = chi.id " +
            "LEFT JOIN School sch ON sch.schoolId = chi.schoolId WHERE  sch.schoolEduId =?1 AND re.reviewStatus = '待复审' AND re.reviewStage = '教育局复审'  ORDER BY re.gmtCreate" )
   List<ReviewInformation> findRwWaitRepDynamicsList(String deptId);



    /**
     * 动态获取已初审核列表
     * @param deptId /
     * @return /
     */
    @Query("FROM ReviewInformation re LEFT JOIN ChildrenInformation chi ON re.childId = chi.id " +
            "LEFT JOIN ReviewInformationLog lg ON re.id = lg.reviewId JOIN School sch ON sch.schoolId = chi.schoolId WHERE sch.deptId =?1 " +
            "AND lg.reviewTime IN( SELECT MAX(lg.reviewTime) FROM ReviewInformationLog lg " +
            "WHERE lg.reviewStage = '学校初审' AND  lg.reviewId = re.id   AND lg.reviewStage = '教育局复审') ORDER BY lg.reviewPersonId desc")
    List<ReviewInformation>  findReAlDynamicsList(Long deptId);



    /**
     * 动态获取已复审核列表
     * @param deptId /
     * @return /
     */
    @Query("FROM ReviewInformation re LEFT JOIN ChildrenInformation chi ON re.childId = chi.id " +
            "LEFT JOIN ReviewInformationLog lg ON re.id = lg.reviewId JOIN School sch ON sch.schoolId = chi.schoolId WHERE sch.schoolEduId =?1 " +
            "AND lg.reviewTime IN( SELECT MAX(lg.reviewTime) FROM ReviewInformationLog lg " +
            "WHERE lg.reviewStage = '学校初审' AND  lg.reviewId = re.id   AND lg.reviewStage = '教育局复审') ORDER BY lg.reviewPersonId desc")
    List<ReviewInformation> findRwAlRepDynamicsList(String deptId);








}